package io.sdph.products.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import java.util.Map.Entry;

import io.sdph.core.database.Transaction;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import io.sdph.core.database.exceptions.DatabaseException;
import io.sdph.products.model.Product;
import io.sdph.userprofile.model.User;

@Repository
public class ProductsManagerDB implements ProductsManagerDAO {
	
	private static final Logger log = Logger.getLogger(ProductsManagerDB.class);

	@Override
	public void saveProduct(final Product product) {
		
		
		if( product.getId() != null){   
			//wariant w ktorym product posiada id ( update starego produktu )
			try {
				Transaction tr_update = new Transaction() {
					@Override
					public Object doIt() throws DatabaseException, SQLException {
						
						//UPDATE tabeli Product
						update("UPDATE Product set name= ?, price= ?,category= ?, descriptionn= ? where idProduct=?;",product.getName(),product.getPrice(),product.getCategory(),product.getDescription(), product.getId());
						
						//Usuniecie starych wartosci z tabeli Pictures
						delete("DELETE FROM Pictures WHERE idProduct= ?;", product.getId());
						
						//Wstawienie  nowych wartosci do tabeli Pictures
						 for(int i=0;product.getPictures().size()>i;i++){
							  
								insert("INSERT INTO Pictures VALUES (?,?);", product.getId(),product.getPictures().get(i));
							   
							   }
						 
						 //Usuniecie starych wartosci z tabeli Details
						 delete("DELETE FROM Details WHERE idProduct= ?;", product.getId());
						 
						 //Dodanie nowych wartosci do tabeli Details
						 
						 if (product.getDetails() != null ){
							for(Entry<String, String> entry : product.getDetails().entrySet()){
								String key = entry.getKey(); 
								String value = entry.getValue(); 
								
							
								insert("INSERT INTO Details VALUES (?,?,?);",product.getId(),key,value);
							
							}}
							
							
							
						 				 
						return null;
					}

				};
		
			} catch (DatabaseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			

		}
		else{
			//wariant w ktorym product nie posiada id (dodanie nowego produktu)

			try {
				Transaction tr_insert = new Transaction() {
					
					@Override
					public Object doIt() throws DatabaseException, SQLException {
						
						// Wstawienie nowych wartosci do Product
						int id = insert("INSERT INTO Product (name,price,category,descriptionn) VALUES ('?','?','?','?');", product.getName(),product.getPrice(),product.getCategory(),product.getDescription());
						
						// Wstawienie noweych wartosci do Picture
						 for(int i=0;product.getPictures().size()>i;i++){
							  
								insert("INSERT INTO Pictures VALUES (?,?);", id,product.getPictures().get(i));
							   
							   }
						//Wstawienie nowych wartosci do Picture
						 if (product.getDetails() != null ){
								for(Entry<String, String> entry : product.getDetails().entrySet()){
									String key = entry.getKey(); 
									String value = entry.getValue(); 
									
								
									insert("INSERT INTO Details VALUES (?,?,?);",id,key,value);
								
								}}
						
						
						
						return null;
					}
				};
			} catch (DatabaseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		}
		
	}

	@Override
	public void removeProduct(final Product product) {
		//W argumencie dostajemy Produkt i usuwamy go z bazy
		try {
			Transaction tr_remove = new Transaction() {
				
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					
					//Usuniecie wartosci z tabeli Product
					delete("DELETE FROM Product  WHERE idProduct= ?;",product.getId());
					//Usuniecie  wartosci z tabeli Pictures
					delete("DELETE FROM Pictures WHERE idProduct= ?;", product.getId());
					
					 //Usuniecie  wartosci z tabeli Details
					 delete("DELETE FROM Details WHERE idProduct= ?;", product.getId());
					
					return null;
				}
			};
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public void fill(final Product product) {
		
		try {
			Transaction tr_get = new Transaction() {
				
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					
					
					// Pobranie rekordu z Product
					ResultSet rs = select("select * from Product where idProduct= ?", product.getId());
					
					if (rs.next()) {
						
						product.setName(rs.getString("name"));
						product.setPrice(rs.getDouble("price"));
						product.setCategory(rs.getString("category"));
						product.setDescription(rs.getString("descriptionn"));

					
					
					//Pobranie wartosci z Pictures
					ResultSet rs2 = select("select * from Pictures where idProduct= ?", product.getId());
					List<String> pictures = new  ArrayList<String>();
					while (rs2.next()){
						 pictures.add( rs2.getString("link") );
					}
					product.setPictures(pictures);
					//Pobranie wartosci z Details
					ResultSet rs3 = select("select * from Details where idProduct= ?", product.getId());
					
					Map<String, String> details= new HashMap<String, String>();
					while (rs3.next()){
						 details.put(rs3.getString("keyy"), rs3.getString("value"));
					}
					product.setDetails(details);
				}else{
					throw new DatabaseException();}
					
					return product;
				}
			};
			
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
	}

	public Product getProduct(final String id){
		Product product = null;
		
		try {
			Transaction tr_get = new Transaction() {
				
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					
					Product product = null;
					// Pobranie rekordu z Product
					ResultSet rs = select("select * from Product where idProduct= ?", id);
					
					if (rs.next()) {
						product = new Product(rs.getString("idProduct"));
						product.setName(rs.getString("name"));
						product.setPrice(rs.getDouble("price"));
						product.setCategory(rs.getString("category"));
						product.setDescription(rs.getString("descriptionn"));

					
					
					//Pobranie wartosci z Pictures
					ResultSet rs2 = select("select * from Pictures where idProduct= ?", id);
					List<String> pictures = new  ArrayList<String>();
					while (rs2.next()){
						 pictures.add( rs2.getString("link") );
					}
					product.setPictures(pictures);
					//Pobranie wartosci z Details
					ResultSet rs3 = select("select * from Details where idProduct= ?", id);
					
					Map<String, String> details= new HashMap<String, String>();
					while (rs3.next()){
						 details.put(rs3.getString("keyy"), rs3.getString("value"));
					}
					product.setDetails(details);
				}else{
					throw new DatabaseException();}
					
					return product;
				}
			};
			product = (Product) tr_get.getResult();
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return product;
		
		
	}
	public ArrayList<Product> getProducts(){
		
		ArrayList<Product> products = null;
		
		
		try {
			Transaction tr_get = new Transaction() {
				
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					ArrayList<Product> products = new ArrayList<Product>();
					
					ResultSet rs = select("select * from Product ");
					Product product=null;
					
					
					while (rs.next()){
						 
						// Pobranie rekordu z Product
						product = new Product(rs.getString("idProduct"));
						product.setName(rs.getString("name"));
						product.setPrice(rs.getDouble("price"));
						product.setCategory(rs.getString("category"));
						product.setDescription(rs.getString("descriptionn"));

					
					
					//Pobranie wartosci z Pictures
					ResultSet rs2 = select("select * from Pictures where idProduct= ?", product.getId());
					List<String> pictures = new  ArrayList<String>();
					while (rs2.next()){
						 pictures.add( rs2.getString("link") );
					}
					product.setPictures(pictures);
					//Pobranie wartosci z Details
					ResultSet rs3 = select("select * from Details where idProduct= ?", product.getId());
					
					Map<String, String> details= new HashMap<String, String>();
					while (rs3.next()){
						 details.put(rs3.getString("keyy"), rs3.getString("value"));
					}
					product.setDetails(details);
						
						
						
						products.add(product);
					}
					
					
					return products;
				}
			};
			products = (ArrayList<Product>) tr_get.getResult();
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		return products;
		
	}
	

}
